<!DOCTYPE html>
<html lang="zh-CN">
<head><meta name="generator" content="Hexo 3.9.0">

    <!--[if lt IE 9]>
        <style>body {display: none; background: none !important} </style>
        <meta http-equiv="Refresh" Content="0; url=//outdatedbrowser.com/" />
    <![endif]-->

<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge, chrome=1">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no">
<meta name="format-detection" content="telephone=no">
<meta name="author" content="Ren ZM">



<meta name="description" content="简单记录MySql语法规则。">
<meta name="keywords" content="note,MySql">
<meta property="og:type" content="article">
<meta property="og:title" content="MySql简单使用">
<meta property="og:url" content="https://littlepro.gitee.io/mytpages/2019/10/24/MySql简单使用/index.html">
<meta property="og:site_name" content="Xbiubiubiu">
<meta property="og:description" content="简单记录MySql语法规则。">
<meta property="og:locale" content="zh-CN">
<meta property="og:updated_time" content="2019-11-02T02:25:30.204Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="MySql简单使用">
<meta name="twitter:description" content="简单记录MySql语法规则。">

<link rel="apple-touch-icon" href="/mytpages//apple-touch-icon.png">


    <link rel="alternate" href="/atom.xml" title="Xbiubiubiu" type="application/atom+xml">



    <link rel="shortcut icon" href="/mytpages//favicon.png">



    <link href="//cdn.bootcss.com/animate.css/3.5.1/animate.min.css" rel="stylesheet">



    <link href="//cdn.bootcss.com/fancybox/2.1.5/jquery.fancybox.min.css" rel="stylesheet">



    <script src="//cdn.bootcss.com/pace/1.0.2/pace.min.js"></script>
    <link href="//cdn.bootcss.com/pace/1.0.2/themes/blue/pace-theme-minimal.css" rel="stylesheet">


<link rel="stylesheet" href="/mytpages/css/style.css">



<link href="//cdn.bootcss.com/font-awesome/4.6.3/css/font-awesome.min.css" rel="stylesheet">


<title>MySql简单使用 | Xbiubiubiu</title>

<script src="//cdn.bootcss.com/jquery/2.2.4/jquery.min.js"></script>
<script src="//cdn.bootcss.com/clipboard.js/1.5.10/clipboard.min.js"></script>

<script>
    var yiliaConfig = {
        fancybox: true,
        animate: true,
        isHome: false,
        isPost: true,
        isArchive: false,
        isTag: false,
        isCategory: false,
        fancybox_js: "//cdn.bootcss.com/fancybox/2.1.5/jquery.fancybox.min.js",
        scrollreveal: "//cdn.bootcss.com/scrollReveal.js/3.1.4/scrollreveal.min.js",
        search: false
    }
</script>


    <script> yiliaConfig.jquery_ui = [false]; </script>



    <script> yiliaConfig.rootUrl = /mytpages/; </script>







    <script src="/mytpages/live2d-widget/autoload.js"></script>


</head></html>
<body>
  <div id="container">
    <div class="left-col">
    <div class="overlay"></div>
<div class="intrude-less">
    <header id="header" class="inner">
        <a href="/mytpages/" class="profilepic">
            <img src="/mytpages//img/avatar.png" class="animated zoomIn">
        </a>
        <hgroup>
          <h1 class="header-author"><a href="/mytpages/">Ren ZM</a></h1>
        </hgroup>

        
        <p class="header-subtitle">--ロリは正義</p>
        

        


        
            <div id="switch-btn" class="switch-btn">
                <div class="icon">
                    <div class="icon-ctn">
                        <div class="icon-wrap icon-house" data-idx="0">
                            <div class="birdhouse"></div>
                            <div class="birdhouse_holes"></div>
                        </div>
                        <div class="icon-wrap icon-ribbon hide" data-idx="1">
                            <div class="ribbon"></div>
                        </div>
                        
                        <div class="icon-wrap icon-link hide" data-idx="2">
                            <div class="loopback_l"></div>
                            <div class="loopback_r"></div>
                        </div>
                        
                        
                        <div class="icon-wrap icon-me hide" data-idx="3">
                            <div class="user"></div>
                            <div class="shoulder"></div>
                        </div>
                        
                    </div>
                    
                </div>
                <div class="tips-box hide">
                    <div class="tips-arrow"></div>
                    <ul class="tips-inner">
                        <li>菜单</li>
                        <li>标签</li>
                        
                        <li>友情链接</li>
                        
                        
                        <li>关于我</li>
                        
                    </ul>
                </div>
            </div>
        

        <div id="switch-area" class="switch-area">
            <div class="switch-wrap">
                <section class="switch-part switch-part1">
                    <nav class="header-menu">
                        <ul>
                        
                            <li><a href="/mytpages//mytpages/../">主页</a></li>
                        
                            <li><a href="/mytpages//mytpages/../archives/">所有文章</a></li>
                        
                            <li><a href="/mytpages//mytpages/../tags/">标签云</a></li>
                        
                            <li><a href="/mytpages//mytpages/../about/">关于我</a></li>
                        
                        </ul>
                    </nav>
                    <nav class="header-nav">
                        <ul class="social">
                            
                                <a class="fa Email" href="mailto:784709355@qq.com" title="Email"></a>
                            
                                <a class="fa GitHub" href="https://github.com/rzzz" title="GitHub"></a>
                            
                                <a class="fa Gitee" href="https://gitee.com/littlepro" title="Gitee"></a>
                            
                                <a class="fa V2EX" href="/mytpages/" title="V2EX"></a>
                            
                                <a class="fa 知乎" href="/mytpages/" title="知乎"></a>
                            
                                <a class="fa 网易云音乐" href="/mytpages/" title="网易云音乐"></a>
                            
                                <a class="fa bilibili" href="/mytpages/" title="bilibili"></a>
                            
                        </ul>
                    </nav>
                </section>
                
                
                <section class="switch-part switch-part2">
                    <div class="widget tagcloud" id="js-tagcloud">
                        <ul class="tag-list"><li class="tag-list-item"><a class="tag-list-link" href="/mytpages/tags/JavaScript/">JavaScript</a></li><li class="tag-list-item"><a class="tag-list-link" href="/mytpages/tags/MySql/">MySql</a></li><li class="tag-list-item"><a class="tag-list-link" href="/mytpages/tags/NodeJS/">NodeJS</a></li><li class="tag-list-item"><a class="tag-list-link" href="/mytpages/tags/Nodejs/">Nodejs</a></li><li class="tag-list-item"><a class="tag-list-link" href="/mytpages/tags/PS3/">PS3</a></li><li class="tag-list-item"><a class="tag-list-link" href="/mytpages/tags/c/">c</a></li><li class="tag-list-item"><a class="tag-list-link" href="/mytpages/tags/c/">c++</a></li><li class="tag-list-item"><a class="tag-list-link" href="/mytpages/tags/cocos2dx/">cocos2dx</a></li><li class="tag-list-item"><a class="tag-list-link" href="/mytpages/tags/markdown/">markdown</a></li><li class="tag-list-item"><a class="tag-list-link" href="/mytpages/tags/note/">note</a></li><li class="tag-list-item"><a class="tag-list-link" href="/mytpages/tags/sort/">sort</a></li><li class="tag-list-item"><a class="tag-list-link" href="/mytpages/tags/test/">test</a></li><li class="tag-list-item"><a class="tag-list-link" href="/mytpages/tags/tool/">tool</a></li><li class="tag-list-item"><a class="tag-list-link" href="/mytpages/tags/usage/">usage</a></li></ul>
                    </div>
                </section>
                
                
                
                <section class="switch-part switch-part3">
                    <div id="js-friends">
                    
                      <a class="main-nav-link switch-friends-link" href="https://hexo.io">Hexo</a>
                    
                      <a class="main-nav-link switch-friends-link" href="https://pages.github.com/">GitHub</a>
                    
                      <a class="main-nav-link switch-friends-link" href="http://moxfive.xyz/">MOxFIVE</a>
                    
                    </div>
                </section>
                

                
                
                <section class="switch-part switch-part4">
                
                    <div id="js-aboutme">ロリは正義</div>
                </section>
                
            </div>
        </div>
    </header>                
</div>
    </div>
    <div class="mid-col">
      <nav id="mobile-nav">
      <div class="overlay">
          <div class="slider-trigger"></div>
          <h1 class="header-author js-mobile-header hide"><a href="/" title="回到主页">Ren ZM</a></h1>
      </div>
    <div class="intrude-less">
        <header id="header" class="inner">
            <a href="/mytpages/" class="profilepic">
                <img src="/mytpages//img/avatar.png" class="animated zoomIn">
            </a>
            <hgroup>
              <h1 class="header-author"><a href="/mytpages/" title="回到主页">Ren ZM</a></h1>
            </hgroup>
            
            <p class="header-subtitle">--ロリは正義</p>
            
            <nav class="header-menu">
                <ul>
                
                    <li><a href="/mytpages/../">主页</a></li>
                
                    <li><a href="/mytpages/../archives/">所有文章</a></li>
                
                    <li><a href="/mytpages/../tags/">标签云</a></li>
                
                    <li><a href="/mytpages/../about/">关于我</a></li>
                
                <div class="clearfix"></div>
                </ul>
            </nav>
            <nav class="header-nav">
                        <ul class="social">
                            
                                <a class="fa Email" target="_blank" href="mailto:784709355@qq.com" title="Email"></a>
                            
                                <a class="fa GitHub" target="_blank" href="https://github.com/rzzz" title="GitHub"></a>
                            
                                <a class="fa Gitee" target="_blank" href="https://gitee.com/littlepro" title="Gitee"></a>
                            
                                <a class="fa V2EX" target="_blank" href="/mytpages/" title="V2EX"></a>
                            
                                <a class="fa 知乎" target="_blank" href="/mytpages/" title="知乎"></a>
                            
                                <a class="fa 网易云音乐" target="_blank" href="/mytpages/" title="网易云音乐"></a>
                            
                                <a class="fa bilibili" target="_blank" href="/mytpages/" title="bilibili"></a>
                            
                        </ul>
            </nav>
        </header>                
    </div>
    <link class="menu-list" tags="标签" friends="友情链接" about="关于我"/>
</nav>
      <div class="body-wrap"><article id="post-MySql简单使用" class="article article-type-post" itemscope itemprop="blogPost">
  
    <div class="article-meta">
      <a href="/mytpages/2019/10/24/MySql简单使用/" class="article-date">
      <time datetime="2019-10-24T02:43:17.000Z" itemprop="datePublished">2019-10-24</time>
</a>


    </div>
  
  <div class="article-inner">
    
      <input type="hidden" class="isFancy" />
    
    
      <header class="article-header">
        
  
    <h1 class="article-title" itemprop="name">
      MySql简单使用
    </h1>
  

      </header>
      
      <div class="article-info article-info-post">
        
    <div class="article-category tagcloud">
    <a class="article-category-link" href="/mytpages/categories/how/">how</a>
    </div>


        
    <div class="article-tag tagcloud">
        <ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/mytpages/tags/MySql/">MySql</a></li><li class="article-tag-list-item"><a class="article-tag-list-link" href="/mytpages/tags/note/">note</a></li></ul>
    </div>

        <div class="clearfix"></div>
      </div>
      
    
    <div class="article-entry" itemprop="articleBody">
      
          
        <p>简单记录MySql语法规则。</p>
<a id="more"></a>

<h3 id="环境前提"><a href="#环境前提" class="headerlink" title="环境前提"></a>环境前提</h3><p>找一个MySql服务器，或者本地安装一个，然后连接上去：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">set names utf8mb4; //设置使用的字符集</span><br><span class="line">show databases; //列出服务器中所有数据库</span><br><span class="line">create database 库名; //创建数据库，创建完成记得要use来切换到此数据库下</span><br><span class="line">drop database 库名; //删除数据库，需要拥有操作权限</span><br><span class="line">use 数据库名; //选择数据库作为当前操作对象</span><br><span class="line">show tables; //列出当前选择的数据库的所有表，后面不再叙述前缀“当前选择的数据库”</span><br><span class="line">describe 表名; //显示数据表的结构,即表头：字段，数据类型，是否允许空，key，默认值，extra</span><br></pre></td></tr></table></figure>

<p>SQL 语句主要可以划分为以下 3 个类别。<br>DDL（Data Definition Languages）语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter等。<br>DML（Data Manipulation Language）语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括 insert、delete、udpate 和select 等。(增添改查）<br>DCL（Data Control Language）语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 grant、revoke 等。</p>
<blockquote>
<p>注意：安装mysql服务器，将自动创建一个mysql数据库，其中有个user表存储mysql服务器的用户</p>
</blockquote>
<h3 id="创建表"><a href="#创建表" class="headerlink" title="创建表"></a>创建表</h3><p>数据类型说明：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line">数值：</span><br><span class="line">    tinyint(size)   //存储-128到127之间的数值，括号与size可以省略，其为显示宽度，如果填值仅代表数据位数长度不到size时前面补零，无数据差别意义；后面追加unsigned代表无符号</span><br><span class="line">    smallint(size)  //同tinyint，-32768到32767 常规，0到65535 无符号</span><br><span class="line">    int(size)       //同tinyint，-2147483648到2147483647常规。0到4294967295无符号</span><br><span class="line">    float(size,d)   //带有浮动小数点的小数字。括号与size可省略，在 size 参数中规定最大位数。在 d 参数中规定小数点右侧的最大位数。无unsigned后缀字选项</span><br><span class="line">    double(size,d)  //同float</span><br><span class="line">字符串：</span><br><span class="line">    char(size)      //保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字节。</span><br><span class="line">    varchar(size)   //保存可变长度的字符串（可包含字母、数字以及特殊字符）。最多65535个字节。</span><br><span class="line">    text            //存放最大长度为 65,535 个字节的字符串。</span><br><span class="line">    longtext        //存放最大长度为 4,294,967,295 个字节的字符串。</span><br><span class="line">时间：</span><br><span class="line">    date            //日期。格式：YYYY-MM-DD，注意它不带有选项，所以没有括号，加括号则用作函数</span><br><span class="line">    time            //时间。格式：HH:MM:SS</span><br><span class="line">    datetime        //日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS</span><br><span class="line">    timestamp       //时间戳。TIMESTAMP 值使用 Unix 纪元(&apos;1970-01-01 00:00:00&apos; UTC) 至今的秒数来存储。</span><br><span class="line">    year            //4位格式所允许的值：1901 到 2155</span><br><span class="line">其他：</span><br><span class="line">    enum(&apos;x&apos;, &apos;y&apos;, &apos;z&apos;, &apos;etc.&apos;)     //允许您输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。</span><br><span class="line">    set(&apos;x&apos;, &apos;y&apos;, &apos;z&apos;, &apos;etc.&apos;)      //基本上为多选的enum，值为x,y,z</span><br><span class="line">    bit(64)                         //最多64为的bit类似</span><br></pre></td></tr></table></figure>

<blockquote>
<p>注意：MySQL中，如果路径中使用”&quot;符号时，这个符号会被过滤。解决的办法是路径中用”/“或”\“来代替”&quot;。这样MySQL就不会自动过滤路径中的分隔符。</p>
</blockquote>
<p>字段修饰和约束：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">修饰：null, not null, unsigned, defalut, auto_increment(自动增长，默认初始为1，每次自动加1，只能用于数值型，一般为主键，插入记录时，传null，或不传)</span><br><span class="line">约束: 主键约束(不能重复,不能为空,只有一个主键)， 唯一键约束(不能重复,可多个唯一键)， 外键约束</span><br></pre></td></tr></table></figure>

<blockquote>
<p>说明：主键的作用是用来确定一条记录的唯一标识，它应该是那些不应该被修改值的字段。</p>
</blockquote>
<p>创建表的几个例子：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">create table t_user(id int not null auto_increment, name varchar(20), phone char(11), primary key (id) );      #单个主键</span><br><span class="line">create table t_user(id int, name varchar(20), phone char(11), primary key(id,name) );  #联合主键</span><br><span class="line">create table t_user(id int, name varchar(20), phone char(11), unique key (phone) );  #手机号码为唯一键</span><br></pre></td></tr></table></figure>

<h3 id="CURD"><a href="#CURD" class="headerlink" title="CURD"></a>CURD</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line">增：</span><br><span class="line">    <span class="keyword">insert</span> <span class="keyword">into</span> 表名(字段<span class="number">1</span>,字段<span class="number">2</span>) <span class="keyword">values</span>(值<span class="number">1</span>,值<span class="number">2</span>),(值<span class="number">1</span>,值<span class="number">2</span>);     //插入多条数据</span><br><span class="line">    <span class="keyword">insert</span> <span class="keyword">into</span> 表名 <span class="keyword">values</span>(值<span class="number">1</span>,值<span class="number">2</span>);                           //针对全表所有字段进行插入操作</span><br><span class="line">    <span class="keyword">insert</span> <span class="keyword">into</span> 表名(字段) <span class="keyword">select</span> 字段 <span class="keyword">from</span> 表<span class="number">2</span>;                //查询结果插入</span><br><span class="line">    <span class="keyword">insert</span> <span class="keyword">into</span> 表名 <span class="keyword">select</span> 字段 <span class="keyword">from</span> 表<span class="number">2</span>;                      //查询结果，全表插入</span><br><span class="line">    说明：表字段经常会有not null,auto_increment等修饰，所以插入数据时要注意修饰词。</span><br><span class="line">删：</span><br><span class="line">    <span class="keyword">delete</span> <span class="keyword">from</span> 表 [<span class="keyword">where</span> 条件];  //删除数据带条件指定数据，否则删除全表数据</span><br><span class="line">改：</span><br><span class="line">    <span class="keyword">update</span> 表 <span class="keyword">set</span> column1=value1,column2=value2 [<span class="keyword">where</span> 条件];  //带条件修改指定数据，否则修改全表</span><br><span class="line">查：</span><br><span class="line">    <span class="keyword">SELECT</span> * <span class="keyword">FROM</span> table_name;   //全部数据</span><br><span class="line">    <span class="keyword">SELECT</span> column_name1,column_name2 <span class="keyword">FROM</span> table_name;   //指定列</span><br><span class="line">    <span class="keyword">SELECT</span> column_name1,column_name2 <span class="keyword">FROM</span> table_name <span class="keyword">ORDER</span> <span class="keyword">BY</span> column_name <span class="keyword">ASC</span>|<span class="keyword">DESC</span>;    //结果排序</span><br><span class="line">    <span class="keyword">SELECT</span> column_name1,column_name2 <span class="keyword">FROM</span> table_name <span class="keyword">WHERE</span> column_name <span class="keyword">operator</span> <span class="keyword">value</span>;      //条件查询</span><br><span class="line">        where子句的条件项可以有多个，用and或者or连接，括号可改变比较优先级，not符号常只和in连用</span><br><span class="line">        operator支持：相等，大小，between(后跟范围)，in(后跟括号，列出可选择项)，like(应用于字符串类型，后跟通配符串)</span><br></pre></td></tr></table></figure>

<h3 id="where子句"><a href="#where子句" class="headerlink" title="where子句"></a>where子句</h3><p>语法规则：WHERE condition1 [AND [OR]] condition2<br>条件关键字：比较运算符, between, in, not in, like</p>
<h3 id="join子句"><a href="#join子句" class="headerlink" title="join子句"></a>join子句</h3><p>最常见的 JOIN 类型：SQL INNER JOIN（简单的 JOIN）、SQL LEFT JOIN、SQL  RIGHT JOIN、SQL FULL JOIN，其中前一种是内连接，后三种是外链接。<br>select * from Table A inner join Table B on Table A.id=Table B.id;<br>select * from Table A left join Table B on Table A.id=Table B.id;<br>select * from Table A right join Table B on Table A.id=Table B.id;<br>select * from Table A full outer join Table B on Table A.id=Table B.id</p>
<h3 id="附加操作"><a href="#附加操作" class="headerlink" title="附加操作"></a>附加操作</h3><p>对结果的二次操作：<br>SELECT name AS n, country AS c FROM Websites;<br>select * from t_user limit 10; // 查询10条数据，索引从0到9，第1条记录到第10条记录<br>select * from t_user limit 0,10; // 同上<br>select * from t_user limit 5,8; // 查询8条数据，索引从5到12，第6条记录到第13条记录<br>UNION 操作符: 用于合并两个或多个 SELECT 语句的结果集。请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。<br>ORDER BY 子句: 来设定你想按哪个字段哪种方式来进行排序</p>
<h3 id="函数"><a href="#函数" class="headerlink" title="函数"></a>函数</h3><p>标量函数(Scalar)</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">UCASE, LCASE, LEN, NOW</span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">UCASE</span>(<span class="keyword">name</span>) <span class="keyword">AS</span> site_title, <span class="keyword">url</span> <span class="keyword">FROM</span> Websites;</span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">name</span>, <span class="keyword">url</span>, <span class="keyword">Now</span>() <span class="keyword">AS</span> <span class="built_in">date</span> <span class="keyword">FROM</span> Websites;</span><br></pre></td></tr></table></figure>

<p>聚合函数(Aggregate)</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">AVG, COUNT, MAX, MIN, SUM</span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">AVG</span>(<span class="keyword">count</span>) <span class="keyword">AS</span> CountAverage <span class="keyword">FROM</span> access_log;</span><br></pre></td></tr></table></figure>

<p>聚合函数追加子句：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">GROUP BY 语句：GROUP BY 语句通常用于结合聚合函数，根据一个或多个列对结果集进行分组，也可不使用聚合函数。</span><br><span class="line">    <span class="keyword">SELECT</span> column_name, aggregate_function(column_name) <span class="keyword">FROM</span> table_name <span class="keyword">WHERE</span> column_name <span class="keyword">operator</span> <span class="keyword">value</span> <span class="keyword">GROUP</span> <span class="keyword">BY</span> column_name;</span><br><span class="line">HAVING 子句：HAVING 子句可以让我们筛选分组后的各组数据，因为不能二次使用where去对分组后的结果再进行筛选了。</span><br><span class="line">    <span class="keyword">SELECT</span> column_name, aggregate_function(column_name) <span class="keyword">FROM</span> table_name <span class="keyword">WHERE</span> column_name <span class="keyword">operator</span> <span class="keyword">value</span> <span class="keyword">GROUP</span> <span class="keyword">BY</span> column_name <span class="keyword">HAVING</span> aggregate_function(column_name) <span class="keyword">operator</span> <span class="keyword">value</span>;</span><br></pre></td></tr></table></figure>


      
    </div>
    
  </div>
  
    


    <nav id="article-nav">
        
            <div id="article-nav-newer" class="article-nav-title">
                <a href="/mytpages/2019/10/24/MySql简单使用-adv/">
                    MySql简单使用-adv
                </a>
            </div>
        
        
            <div id="article-nav-older" class="article-nav-title">
                <a href="/mytpages/2019/10/18/PS3-HEN破相关记录/">
                    PS3-HEN破相关记录
                </a>
            </div>
        
    </nav>

  
</article>

    <div id="toc" class="toc-article">
        <strong class="toc-title">文章目录</strong>
        
            <ol class="toc"><li class="toc-item toc-level-3"><a class="toc-link" href="#环境前提"><span class="toc-number">1.</span> <span class="toc-text">环境前提</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#创建表"><span class="toc-number">2.</span> <span class="toc-text">创建表</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#CURD"><span class="toc-number">3.</span> <span class="toc-text">CURD</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#where子句"><span class="toc-number">4.</span> <span class="toc-text">where子句</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#join子句"><span class="toc-number">5.</span> <span class="toc-text">join子句</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#附加操作"><span class="toc-number">6.</span> <span class="toc-text">附加操作</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#函数"><span class="toc-number">7.</span> <span class="toc-text">函数</span></a></li></ol>
        
    </div>
    <style>
        .left-col .switch-btn,
        .left-col .switch-area {
            display: none;
        }
        .toc-level-3 i,
        .toc-level-3 ol {
            display: none !important;
        }
    </style>

    <input type="button" id="tocButton" value="隐藏目录"  title="点击按钮隐藏或者显示文章目录">

    <script>
        yiliaConfig.toc = ["隐藏目录", "显示目录", !!"false"];
    </script>



    
<div class="share">
    
        <div class="bdsharebuttonbox">
            <a href="#" class="fa fa-twitter bds_twi" data-cmd="twi" title="分享到推特"></a>
            <a href="#" class="fa fa-weibo bds_tsina" data-cmd="tsina" title="分享到新浪微博"></a>
            <a href="#" class="fa fa-qq bds_sqq" data-cmd="sqq" title="分享给 QQ 好友"></a>
            <a href="#" class="fa fa-files-o bds_copy" data-cmd="copy" title="复制网址"></a>
            <a href="#" class="fa fa fa-envelope-o bds_mail" data-cmd="mail" title="通过邮件分享"></a>
            <a href="#" class="fa fa-weixin bds_weixin" data-cmd="weixin" title="生成文章二维码"></a>
            <a href="#" class="fa fa-share-alt bds_more" data-cmd="more"></i></a>
        </div>
        <script>
            window._bd_share_config={
                "common":{"bdSnsKey":{},"bdText":"MySql简单使用　| Xbiubiubiu　","bdMini":"2","bdMiniList":false,"bdPic":"","bdStyle":"0","bdSize":"24"},"share":{}};with(document)0[(getElementsByTagName('head')[0]||body).appendChild(createElement('script')).src='http://bdimg.share.baidu.com/static/api/js/share.js?v=89860593.js?cdnversion='+~(-new Date()/36e5)];
        </script>
    

    
</div>







    




    <div class="scroll" id="post-nav-button">
        
            <a href="/mytpages/2019/10/24/MySql简单使用-adv/" title="上一篇: MySql简单使用-adv">
                <i class="fa fa-angle-left"></i>
            </a>
        

        <a title="文章列表"><i class="fa fa-bars"></i><i class="fa fa-times"></i></a>

        
            <a href="/mytpages/2019/10/18/PS3-HEN破相关记录/" title="下一篇: PS3-HEN破相关记录">
                <i class="fa fa-angle-right"></i>
            </a>
        
    </div>

    <ul class="post-list"><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/12/13/cpp：allocator-new/">cpp：allocator,new</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/11/28/CPP标准库-note/">CPP标准库-note</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/11/27/C与win32API/">C与win32API</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/11/27/C标准库-note/">C标准库</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/11/26/cpp的lib和dll/">cpp的lib和dll</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/11/25/编译cpp-vs2015/">编译cpp-vs2015</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/11/23/编译cpp-准备/">编译c++准备</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/11/18/simple-opengl/">simple opengl</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/11/15/cocos2dx-action/">cocos2dx-action</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/11/15/cocos2dx-schedule/">cocos2dx-schedule</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/11/14/cocos2dx在win32上写日志文件/">cocos2dx在win32上写日志文件</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/11/14/cocos2dx在win32上播放ogg/">cocos2dx在win32上播放ogg</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/11/14/cocos2dx字库缺失文字处理/">cocos2dx字库缺失文字处理</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/11/14/cocos2dx工作目录修改/">cocos2dx工作目录修改</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/11/13/cocos2dx-android/">cocos2dx-android</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/11/12/cocos2dx-win32/">cocos2dx-win32</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/11/11/Sublimer-Text/">Sublimer Text</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/11/07/潜意识？？设计模式/">潜意识？？设计模式</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/11/04/BST？AVL？红黑树？/">BST？AVL？红黑树？</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/11/01/几种排序算法示例代码/">几种排序算法示例代码</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/10/31/引子-基础算法？？/">引子-基础算法？？</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/10/31/引子-数据结构？？/">引子-数据结构？？</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/10/25/不会懂分布式系统的/">不会懂分布式系统的</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/10/24/MySql简单使用-adv/">MySql简单使用-adv</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/10/24/MySql简单使用/">MySql简单使用</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/10/18/PS3-HEN破相关记录/">PS3-HEN破相关记录</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/10/09/测试使用云函数扩展码云page/">测试使用云函数扩展码云page</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/10/09/web3-单页Web应用/">web3.单页Web应用</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/09/29/web2-简单网站的前后端分离/">web2.简单网站的前后端分离</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/09/29/web1-创建一个简单的网站/">web1.创建一个简单的网站</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/09/27/NodeJS-简单使用/">NodeJS 简单使用</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/09/27/JavaScript-other/">JavaScript-other</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/09/27/JavaScript-模块/">JavaScript-模块</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/09/27/JavaScript-简单使用/">JavaScript 简单使用</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/09/27/github-gitee-chrome文件树插件/">github & gitee chrome文件树插件</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/09/26/markdown-简单使用/">markdown 简单使用示例</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/09/25/hello-world/">Hello World</a></li><li class="post-list-item"><a class="post-list-link" href="/mytpages/2019/09/24/zzTest-article0/">zzTest article0</a></li></ul>




    <script>
        
    </script>
</div>
      <footer id="footer">
    <div class="outer">
        <div id="footer-info">
            <div class="footer-left">
                <i class="fa fa-copyright"></i> 
                2019-2020 Ren ZM
            </div>
            <div class="footer-right">
                <a href="http://hexo.io/" target="_blank" title="快速、简洁且高效的博客框架">Hexo</a>  Theme <a href="https://github.com/MOxFIVE/hexo-theme-yelee" target="_blank" title="简而不减 Hexo 双栏博客主题  v3.5">Yelee</a> by MOxFIVE <i class="fa fa-heart animated infinite pulse"></i>
            </div>
        </div>
        
            <div class="visit">
                
                    <span id="busuanzi_container_site_pv" style='display:none'>
                        <span id="site-visit" title="本站到访数"><i class="fa fa-user" aria-hidden="true"></i><span id="busuanzi_value_site_uv"></span>
                        </span>
                    </span>
                
                
                    <span>| </span>
                
                
                    <span id="busuanzi_container_page_pv" style='display:none'>
                        <span id="page-visit"  title="本页阅读量"><i class="fa fa-eye animated infinite pulse" aria-hidden="true"></i><span id="busuanzi_value_page_pv"></span>
                        </span>
                    </span>
                
            </div>
        
    </div>
</footer>
    </div>
    
<script data-main="/mytpages/js/main.js" src="//cdn.bootcss.com/require.js/2.2.0/require.min.js"></script>

    <script>
        $(document).ready(function() {
            var iPad = window.navigator.userAgent.indexOf('iPad');
            if (iPad > -1 || $(".left-col").css("display") === "none") {
                var bgColorList = ["#9db3f4", "#414141", "#e5a859", "#f5dfc6", "#c084a0", "#847e72", "#cd8390", "#996731"];
                var bgColor = Math.ceil(Math.random() * (bgColorList.length - 1));
                $("body").css({"background-color": bgColorList[bgColor], "background-size": "cover"});
            }
            else {
                var backgroundnum = 5;
                var backgroundimg = "url(/mytpages//background/bg-x.jpg)".replace(/x/gi, Math.ceil(Math.random() * backgroundnum));
                $("body").css({"background": backgroundimg, "background-attachment": "fixed", "background-size": "cover"});
            }
        })
    </script>





<div class="scroll" id="scroll">
    <a href="#" title="返回顶部"><i class="fa fa-arrow-up"></i></a>
    <a href="#comments" onclick="load$hide();" title="查看评论"><i class="fa fa-comments-o"></i></a>
    <a href="#footer" title="转到底部"><i class="fa fa-arrow-down"></i></a>
</div>
<script>
    // Open in New Window
    
        var oOpenInNew = {
            
            
            
            
            
            
             archives: ".archive-article-title", 
             miniArchives: "a.post-list-link", 
            
             friends: "#js-friends a", 
             socail: ".social a" 
        }
        for (var x in oOpenInNew) {
            $(oOpenInNew[x]).attr("target", "_blank");
        }
    
</script>

<script async src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js">
</script>
  </div>
</body>
</html>